# 海鲜

aaa=sqlContext.sql("""
    create table haixian.all_20181217 as
    select
        *
    FROM
        dw.sms_log
    where
      msg_content LIKE '%盒马生鲜%'
      OR msg_content LIKE '%海鲜%'
      OR msg_content LIKE '%超级物种%'
      OR msg_content LIKE '%永辉%'
      OR msg_content LIKE '%獐子岛%'
      OR msg_content LIKE '%中水%'
      OR msg_content LIKE '%好当家%'
      OR msg_content LIKE '%明珠%'
      OR msg_content LIKE '%棒棰岛%'
      OR msg_content LIKE '%远洋%'
      OR msg_content LIKE '%东方海洋%'
      OR msg_content LIKE '%大洋世家%'
      OR msg_content LIKE '%鱼老板%'
      OR msg_content LIKE '%海八鲜%'
""")

aaa=sqlContext.sql("""
    create table haixian.filter_20181218 as
    select
        *
    FROM
        haixian.all_20181217
    where
    smslabel = '永辉超市'
    OR smslabel = '永辉生活'
    OR smslabel = '永辉会员店'
    OR smslabel = '淘宝'
    OR smslabel = '半岛翡丽'
    OR smslabel = '地球港'
    OR smslabel = '永辉钱包'
    OR smslabel = '海昱昌隆'
    OR smslabel = '金紫银海鲜顺义店'
    OR smslabel = '新乐国旅'

""")



aaa=sqlContext.sql("""
    create table haixian.haixiandian_20181218 as
    select
        *
    FROM
        dw.sms_log
    where
 smslabel = '上海富友'
OR smslabel = '柒海鲜'
OR smslabel = '东南海鲜馆'
OR smslabel = '珠海广电网络'
OR smslabel = '讨海人海鲜特产'
OR smslabel = '湛江海鲜干货'
OR smslabel = '加利利海鲜店'
OR smslabel = '亲亲鱼海鲜'
OR smslabel = '翔彦期海鲜众筹'
OR smslabel = '蚌埠海亮'
OR smslabel = '奇盟温州海鲜'
OR smslabel = '众福海产品'
OR smslabel = '新顺风海鲜酒楼'
OR smslabel = '老许海参'
OR smslabel = '东山海鲜专供'
OR smslabel = '上海银行'
OR smslabel = '椒炉海鲜自助火锅'
OR smslabel = '华鑫海鲜商贸'
OR smslabel = '重庆永逸电器有限公司海尔专卖店'
OR smslabel = '上海海王星辰'
OR smslabel = '幸福渔家海鲜批发'
OR smslabel = '宝鸡于记海鲜公园路店'
OR smslabel = '潮舟量贩海鲜酒楼'
OR smslabel = '威海高哥'
OR smslabel = '海尔'
OR smslabel = '东山岛海鲜专供'
OR smslabel = '贝壳王国海鲜餐厅'
OR smslabel = '渔人海鲜'
OR smslabel = '老百姓海鲜城'
OR smslabel = '海边直播'
OR smslabel = '南京海王星辰'
OR smslabel = '安徽省上海商会'
OR smslabel = '台州海鲜楼'
OR smslabel = '艎海传奇火锅餐厅'
OR smslabel = '皮皮中韩海鲜零食'
OR smslabel = '辣近海鲜'
OR smslabel = '船小二海鲜'
OR smslabel = '荣昌小海鲜'
OR smslabel = '珍宝海鲜'
OR smslabel = '陈鲜生石浦海鲜'
OR smslabel = '宝鸡于记海鲜高新店'
OR smslabel = '逸淳海鲜'
OR smslabel = '非常海'
OR smslabel = '靓蒸鲜原味蒸海鲜'
OR smslabel = '空中海鲜餐厅'
OR smslabel = '保定小海鲜'
OR smslabel = '赶蟹手抓海鲜餐吧'
OR smslabel = '新温州港海鲜量贩'
OR smslabel = '蚝鱻香海鲜'
OR smslabel = '花甲客时尚海鲜餐厅'
OR smslabel = '网潮海鲜'
OR smslabel = '海食家'
OR smslabel = '鲜鱼坊海鲜商城'
OR smslabel = '老马海鲜'
OR smslabel = '海有渔家'
OR smslabel = '上海同鑫'
OR smslabel = '悦鼎会海鲜姿造'
OR smslabel = '26楼海鲜家常菜'
OR smslabel = '买鲜海外旗舰店'
OR smslabel = '棒棰岛上海旗舰店'
OR smslabel = '海买进口海鲜'
OR smslabel = '德月海鲜'
OR smslabel = '海鲜人家水产店'
OR smslabel = '海之媛小海鲜'
OR smslabel = '97海鲜'
OR smslabel = '天天海鲜城'
OR smslabel = '海食家臻品店'
OR smslabel = '渔季海鲜'
OR smslabel = '利玛海鲜大酒店'
OR smslabel = '码头海鲜广场'
OR smslabel = '海尔电器'
OR smslabel = '东港宏泽海鲜'
OR smslabel = '海味优品水产直销'
OR smslabel = '胡记海鲜'
OR smslabel = '海南旅游'
OR smslabel = '北方海鲜商城'
OR smslabel = '老船长海鲜家长菜'
OR smslabel = '鲜乐缘海鲜'
OR smslabel = '海森博蒸汽海鲜城'
OR smslabel = '盛宴海鲜'
OR smslabel = '烨佳海鲜'
OR smslabel = '渔夫海鲜老店'
OR smslabel = 'Sao厨麻辣海鲜'
OR smslabel = '海上云起海鲜电商'
OR smslabel = '海头海州湾海鲜店'
OR smslabel = '沿海所'
OR smslabel = '渔岩海鲜'
OR smslabel = '安美国际海外专营店'
OR smslabel = '志合小海鲜饺子城'
OR smslabel = '云码头海鲜'
OR smslabel = '章记海鲜'
OR smslabel = '海鲜时代'
OR smslabel = '新亚海鲜坊'
OR smslabel = '上海展业'
OR smslabel = '菜船长海鲜'
OR smslabel = '阿森海鲜'
OR smslabel = '柒加海鲜'
OR smslabel = '博贺港海鲜特产'
OR smslabel = '海澄轩'
OR smslabel = '火乐生活海外专营店'
OR smslabel = '珠海阳光国旅'
OR smslabel = '海鲜新鲜货'
OR smslabel = '滨海区研究督查室'
OR smslabel = '海风教育'
OR smslabel = '大连棒棰岛海参'
OR smslabel = '和久海鲜之家'
OR smslabel = '三太子海鲜干货'
OR smslabel = '舟山红老大海鲜坊'
OR smslabel = '醉江南海鲜酒店院桥店'
OR smslabel = '小海鲜餐饮集团'
OR smslabel = '王老太麻辣小海鲜品牌店'
OR smslabel = '乳山海鲜汇食品店'
OR smslabel = '融城海鲜楼'
OR smslabel = '海鲜送到家'
OR smslabel = '滨江海鲜大酒店'
OR smslabel = '三条小船大连海鲜特产'
OR smslabel = '阳光海鲜城'
OR smslabel = '万龙洲海鲜望京店'
OR smslabel = '禾澳进口海鲜'
OR smslabel = '威海优全生鲜'
OR smslabel = '鑫赏面店海鲜部'
OR smslabel = '依梦小海鲜'
OR smslabel = '耿记青岛海鲜城'
OR smslabel = '三门小杨海鲜'
OR smslabel = '七福徕牛排海鲜自助'
OR smslabel = '再回首烧烤海鲜龙虾'
OR smslabel = '金紫银海鲜'
OR smslabel = '蟹之都海鲜礼盒'
OR smslabel = '李记海鲜九天店福利大放送'
OR smslabel = '日照海鲜干货'
OR smslabel = '小鲜女海味铺'
OR smslabel = '三立海鲜大牌档'
OR smslabel = '青岛正宗海鲜'
OR smslabel = '三立海鲜美食广场'
OR smslabel = '海外海集团'
OR smslabel = '海码头海鲜酒店'
OR smslabel = '进来海鲜酒楼'
OR smslabel = '铭杰海鲜'
OR smslabel = '台州路桥自家人海鲜城'
OR smslabel = '海景沙滩旗舰店'
OR smslabel = '老王大连海参'
OR smslabel = '正亮海鲜'
OR smslabel = '漫海芸蒸海鲜楼'
OR smslabel = '世贸物业东海徽园'
OR smslabel = '东山海鲜'
OR smslabel = '新版海归协会客户端'
OR smslabel = '野生海参海鲜大闸蟹'
OR smslabel = '海味之乡'
OR smslabel = '陈记海鲜'
OR smslabel = '鲅鱼圈小海鲜'
OR smslabel = '四更天海鲜'
OR smslabel = '东山岛海鲜'
OR smslabel = '李记海鲜九天店'
OR smslabel = '时尚海鲜餐厅'
OR smslabel = '陆龙海产旗舰店'
OR smslabel = '趴趴熊海外淘'
OR smslabel = '鹏鹏海鲜'
OR smslabel = '新亚海鲜舫'
OR smslabel = '海鲜大食代'
OR smslabel = '御珍鲜海鲜'
OR smslabel = '子渔爷爷海鲜熟食企业店'
OR smslabel = '海鲜码头'
OR smslabel = '蟹之都'
OR smslabel = '老船客旗舰店'
OR smslabel = '蟹之家'
OR smslabel = '鲜帝食品专营店'
OR smslabel = '渔公码头'
OR smslabel = '当日鲜'
OR smslabel = '远洋品味食品专营店'
OR smslabel = '食行生鲜'
""")





import pandas as pd
from pyspark import SparkContext, SparkConf
from pyspark.sql import HiveContext
from pyspark.sql import SQLContext
import sys

reload(sys)
import xlrd
import os


df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉超市%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉超市.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%null%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/null.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉生活%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉生活.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉会员店%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉会员店.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%淘宝%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/淘宝.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%掌通家园%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/掌通家园.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%半岛翡丽%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/半岛翡丽.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%欧珀莱%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/欧珀莱.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%韵达快递%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/韵达快递.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%国家电网福建公司%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/国家电网福建公司.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%地球港%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/地球港.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉金融公司%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉金融公司.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉钱包%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉钱包.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%海昱昌隆%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/海昱昌隆.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉金融%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉金融.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%康佰家%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/康佰家.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%金紫银海鲜顺义店%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/金紫银海鲜顺义店.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%速递易%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/速递易.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%新乐国旅%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/新乐国旅.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%金紫银通州店%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/金紫银通州店.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%永辉全球购%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/永辉全球购.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
df=sqlContext.sql("""SELECT * FROM haixian.all_20181217 where msg_content like  '%菜鸟驿站%' LIMIT 10000""")
writer = pd.ExcelWriter("""/home/extract/haixian/菜鸟驿站.xlsx""" ,engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()
